﻿CREATE PROCEDURE [dbo].[GetRecentSaleProperty]
@AddressKeyword nvarchar(50),
@PropertyType nvarchar(50)= 'LAIN-LAIN',
@Daerah nvarchar(50) = '',
@Limit int,
@UserID bigint=0
AS
BEGIN

	SET NOCOUNT ON;
-- INSERT QUERY INFO INTO Mobile_UserQueryTransact
INSERT INTO Mobile_UserQueryTransact(UserID,AddressKeyword, JenisHartanah, TransactionType)
VALUES (@UserID,@AddressKeyword + ',' + @Daerah,@PropertyType,1)
	
IF @PropertyType='LAIN-LAIN'

	select top (@Limit) 
	JenisHartanah as LandType,
	JenisBangunan as PropertyType,
	LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))) as Alamat ,Kawasan ,Daerah,
	ISNULL(LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))),'') + ',' + ISNULL(Kawasan,'') + ',' + ISNULL(Daerah,'') as Address,
	Balasan as Price,
	TkhPenilaian as TransactionDate, 
	LuasLot  as Land,
	LuasBgnUtama  as BuildUp,
	BilBilikTidur as Bedroom
	from AW_Auction
	where (JenisHartanah = @PropertyType or JenisHartanah IS NULL) and
	(Alamat like '%' + @AddressKeyword+ '%' or Kawasan like '%'+@AddressKeyword+'%' or Daerah like '%'+@AddressKeyword+'%') and
	(@Daerah = '' or Daerah = @Daerah)
	order by TkhPenilaian desc;

ELSE

	select top (@Limit) 
	JenisHartanah as LandType,
	JenisBangunan as PropertyType,
	LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))) as Alamat ,Kawasan ,Daerah,
	ISNULL(LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))),'') + ',' + ISNULL(Kawasan,'') + ',' + ISNULL(Daerah,'') as Address,
	Balasan as Price,
	TkhPenilaian as TransactionDate, 
	LuasLot  as Land,
	LuasBgnUtama  as BuildUp,
	BilBilikTidur as Bedroom
	from AW_Auction
	where JenisHartanah = @PropertyType and
	(Alamat like '%' + @AddressKeyword+ '%' or Kawasan like '%'+@AddressKeyword+'%' or Daerah like '%'+@AddressKeyword+'%') and
	(@Daerah = '' or Daerah = @Daerah)
	order by TkhPenilaian desc;

END
